# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html


# class EtherscanSpiderPipeline(object):
#     def process_item(self, item, spider):
#         print(item)
from logging import log

import pymysql
from etherscan_spider import settings

class BlockinfoPipeline(object):

    def __init__(self):
        # 连接数据库
        self.connect = pymysql.connect(host=settings.MYSQL_HOST,
                                       db=settings.MYSQL_DBNAME,
                                       user=settings.MYSQL_USER,
                                       passwd=settings.MYSQL_PASSWD,
                                       charset='utf8',
                                       use_unicode=True)

        # 通过cursor执行增删查改
        self.cursor = self.connect.cursor()

    def close_spider(self, spider):
        # 关闭爬虫时执行
        # 1 去重
        sql = """
        delete from block where height in(
            select t.height from(
                select height from block group by height having count(*)>1
            ) t
        )and block_id not in(
            select t.block_id from(
                select max(block_id) as block_id from block group by height having count(*)>1
            ) t
        );"""
        try:
            self.cursor.execute(sql)
            self.connect.commit()
        except Exception as error:

            print(error)
        # 2 关闭连接
        self.cursor.close()
        self.connect.close()

    def process_item(self, item, spider):
        print(item)
        sql = "insert into block(height,timestamp,transaction_num," \
              "internal_transaction_num,hash,parent_hash,mined_by," \
              "difficulty,total_difficulty,size,gas_used,gas_limit," \
              "nonce,block_reward,extra_date,ether_txfee_avg,other_contract_txfee_avg," \
              "all_txfee_avg,contract_persent) value (%s,%s,%s,%s,'%s','%s','%s','%s','%s',%s,%s,%s,'%s',%s,'%s;',%s,%s,%s,%s)"
        data = (item['height'],item['timestamp'],item['transaction_num'],item['internal_transaction_num'],item['hash'],
                item['parent_hash'],item['mined_by'],item['difficulty'],item['total_difficulty'],item['size'],
                item['gas_used'],item['gas_limit'],item['nonce'],item['block_reward'],item['extra_date'],
                item['ether_txfee_avg'],item['other_contract_txfee_avg'],item['all_txfee_avg'],item['contract_persent'],)
        try:
            # 插入数据
            print(sql % data)
            self.cursor.execute(sql % data)
            self.connect.commit()
        except Exception as error:
            # 出现错误时打印错误日志
            # logger
            print(error)


